package com.cn.delan.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import javax.sql.DataSource;

/**
 * JDBC工具类
 * @author BingGo
 * @version 1.0
 */
public abstract class JDBCUtil {
	/**
	 * JDBC数据源
	 */
	private DataSource dataSource;
	/**
	 * 每页数据条数,默认为10条
	 */
	private int pageCount = 10;
	/**
	 * 翻页工具类,用于compile方法中对SQL语句进行分页处理
	 */
	private Page page;
	/**
	 * 当前执行的SQL语句
	 */
	private String sql;
	/**
	 * 当前执行SQL语句所用参数
	 */
	private Object[] params;
	
	/**
	 * 将JDBC数据源作为构造参数传入
	 * @param dataSource JDBC数据源
	 */
	public JDBCUtil(DataSource dataSource){
		this.dataSource = dataSource;
	}
	/**
	 * 根据SQL语句返回对应第一页的结果集
	 * @param sql SQL语句
	 * @return 对应的结果集
	 * @throws SQLException
	 */
	public RowList query(String sql) throws SQLException{
		return this.query(sql,1);
	}
	/**
	 * 根据SQL语句和页码返回对应的结果集,此方法是同步的为了避免compile时用到pageUtil中的信息时出错。
	 * @param sql SQL语句
	 * @param pageNo 页码
	 * @return 对应的结果集
	 * @throws SQLException
	 */
	public RowList query(String sql,int pageNo) throws SQLException{
		RowList list = new RowList();
		this.sql = sql;
		this.params = null;
		//获取记录总数
		int total = this.count(sql);
		//必须先于compile进行调用，因为compile中会用到此对象
		this.page = this.getPage(pageNo, this.getPageCount(), total);
		//分页编译
		String compileSQL = this.compile(sql);
		//设置分页信息
		list.setPageCount(this.getPageCount());
		list.setPageNo(pageNo);
		list.setTotal(total);
		list.setPageTotal(this.page.getPageTotal());
		Connection conn = this.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(compileSQL);
		//创建表头部信息
		ResultSetMetaData rsmd = rs.getMetaData();
		Header header = this.createHeader(rsmd);
		this.fillHeader(header);
		list.setHeader(header);
		//获取记录信息
		this.fillRows(rs, header, list);
		stmt.close();
		conn.close();
		return list;
	}
	/**
	 * 表头信息填充,根据不同的应用系统进行不同的填充实现.
	 * @param header 表头信息
	 * @return 填充后的表头信息。
	 */
	protected Header fillHeader(Header header){
		//TODO 此方法需要被继承以实现真实填充，也可以采用后控调用的方式
		return header;
	}
	public RowList query(String sql,Object[] params) throws SQLException{
		return this.query(sql,params,1);
	}
	public RowList query(String sql,Object[] params,int pageNo,int pageCount) throws SQLException{
		this.setPageCount(pageCount);
		return this.query(sql,params,pageNo);
	}
	public RowList query(String sql,Object[] params,int pageNo) throws SQLException{
		RowList list = new RowList();
		this.sql = sql;
		this.params = params;
		//获取记录总数
		int total = this.count(sql,params);
		//必须先于compile进行调用，因为compile中会用到此对象
		this.page = this.getPage(pageNo, this.getPageCount(), total);
		//分页编译
		String compileSQL = this.compile(sql);
		//设置分页信息
		list.setPageCount(this.getPageCount());
		list.setPageNo(pageNo);
		list.setTotal(total);
		list.setPageTotal(this.page.getPageTotal());
		Connection conn = this.getConnection();
		PreparedStatement pstmt = conn.prepareStatement(compileSQL);
		//填充参数
		this.fillParameters(pstmt, params);
		ResultSet rs = pstmt.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		//创建表头部信息
		Header header = this.createHeader(rsmd);
		this.fillHeader(header);
		list.setHeader(header);
		//获取记录信息
		this.fillRows(rs, header, list);
		pstmt.close();
		conn.close();
		return list;
	}
	public int update(String sql) throws SQLException{
		int count = 0;
		this.sql = sql;
		this.params = null;
		Connection conn = this.getConnection();
		Statement stmt = conn.createStatement();
		count = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return count;
	}
	public int update(String sql,Object[] params) throws SQLException{
		int count = 0;
		this.sql = sql;
		this.params = params;
		Connection conn = this.getConnection();
		PreparedStatement pstmt = conn.prepareStatement(sql);
		this.fillParameters(pstmt, params);
		count = pstmt.executeUpdate();
		pstmt.close();
		conn.close();
		return count;
	}
	/**
	 * SQL语句分页编译,不同的数据库需要重写此方法。
	 * @param sql 普通SQL语句
	 * @return 经过分页处理的SQL语句
	 * @see Page getPageUtil(int pageNo,int pageCount,int total)
	 */
	protected abstract String compile(String sql);
	/**
	 * 计算检索结果的数量,不同的数据库可能需要重写此方法。
	 * @param sql 普通SQL语句
	 * @return 检索结果的总数
	 * @throws SQLException
	 */
	protected int count(String sql) throws SQLException{
		int count = 0;
		Connection conn = this.getConnection();
		Statement stmt = conn.createStatement();
		//备用,正则替换的方式可能存在BUG
		//sql = "select count(1) from ("+sql+") as _sqltable";
		sql = sql.replaceAll("(select )[\\S]+( from .+)", "$1count(1)$2");
		ResultSet rs = stmt.executeQuery(sql);
		if(rs.next()){
			count = rs.getInt(1);
		}
		stmt.close();
		conn.close();
		return count;
	}
	/**
	 * 计算检索结果的数量,不同的数据库可能需要重写此方法。
	 * @param sql 普通SQL语句
	 * @param params SQL语句参数
	 * @return 检索结果的总数
	 * @throws SQLException
	 */
	protected int count(String sql,Object[] params) throws SQLException{
		int count = 0;
		Connection conn = this.getConnection();
		//备用,正则替换的方式可能存在BUG
		//sql = "select count(1) from ("+sql+") as _sqltable";
		sql = sql.replaceAll("(select )[\\S]+( from .+)", "$1count(1)$2");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		//填充参数
		this.fillParameters(pstmt, params);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			count = rs.getInt(1);
		}
		pstmt.close();
		conn.close();
		return count;
	}
	/**
	 * 创建表头信息
	 * @param rsmd 结果集数据定义
	 * @return 返回装载后的表头信息
	 * @throws SQLException
	 */
	private Header createHeader(ResultSetMetaData rsmd) throws SQLException{
		//表中注入项的数量
		int cellCount = rsmd.getColumnCount();
		Header header = new Header();
		//获取表头部信息
		for(int i=0;i<cellCount;i++){
			String colId = rsmd.getColumnLabel(i+1).toLowerCase();
			int colType = rsmd.getColumnType(i+1);
			//FIXME 可能需要抽象出来不同的数据库其支持程度不同
			String tableId = rsmd.getTableName(i+1);
			ColumnDetail headerDetail = new ColumnDetail(colId,colType);
			headerDetail.setTableId(tableId);
			headerDetail.setColName(colId);
			header.put(colId, headerDetail);
		}
		return header;
	}
	/**
	 * 装载行信息
	 * @param rs 检索结果集
	 * @param header 头信息，其中包含列的相关信息
	 * @param list 将rs产生的行信息装载到此对象中。
	 * @throws SQLException
	 */
	private void fillRows(ResultSet rs,Header header,RowList list) throws SQLException{
		//避免游标不是从头开始
		rs.beforeFirst();
		//获取记录信息
		while(rs.next()){
			Row row = new Row();
			for(String colId:header.keySet()){
				//存放一个注入项
				row.addCell(colId, this.getValue(rs, colId, header.get(colId).getColType()));
			}
			//存放一条记录
			list.addRow(row);
		}
	}
	/**
	 * 为PreparedStatement对象装载参数
	 * @param pstmt 装载如的对象
	 * @param params 参数集合
	 * @throws SQLException
	 */
	private void fillParameters(PreparedStatement pstmt,Object[] params) throws SQLException{
		int parameterIndex = 1;
		if(params!=null){
			for(Object p:params){
				if(p==null){
					continue;
				}
				switch(this.getType(p)){
					case Types.INTEGER:
						pstmt.setInt(parameterIndex++, (Integer)p);
						break;
					case Types.VARCHAR:
						pstmt.setString(parameterIndex++, (String)p);
						break;
					case Types.DOUBLE:
						pstmt.setDouble(parameterIndex++, (Double)p);
						break;
					case Types.FLOAT:
						pstmt.setFloat(parameterIndex++, (Float)p);
						break;
					case Types.DATE:
						pstmt.setDate(parameterIndex++, (Date)p);
						break;
					case Types.TIMESTAMP:
						pstmt.setTimestamp(parameterIndex++, (Timestamp)p);
						break;
				}
			}
		}
	}
	/**
	 * 根据java类返回对应的数据字段类型
	 * @param obj java对象
	 * @return 数据库对应的类型
	 */
	private int getType(Object obj){
		if(obj instanceof String){
			return Types.VARCHAR;
		}
		if(obj instanceof Integer){
			return Types.INTEGER;
		}
		if(obj instanceof Float){
			return Types.FLOAT;
		}
		if(obj instanceof Double){
			return Types.DOUBLE;
		}
		if(obj instanceof Date){
			return Types.DATE;
		}
		if(obj instanceof Timestamp){
			return Types.TIMESTAMP;
		}
		return Types.OTHER;
	}
	/**
	 * 获取分页工具集,记录编号从0开始。记录编号若从1开始需要重写此方法进行对应的处理
	 * @param pageNo 页码
	 * @param pageCount 每页条数
	 * @param total 检索总数
	 * @return 分页工具集
	 */
	private Page getPage(int pageNo,int pageCount,int total){
		return new Page(pageNo,pageCount,total);
	}
	/**
	 * 根据字段类型,获取记录中指定字段值
	 * @param rs 结果集
	 * @param key 字段ID
	 * @param type 字段类型
	 * @return 字段值
	 * @throws SQLException
	 */
	protected Object getValue(ResultSet rs,String key,int type) throws SQLException{
		Object value = null;
		switch(type){
			case Types.VARCHAR:
				value = rs.getString(key);
				break;
			case Types.INTEGER:
				value = rs.getInt(key);
				break;
			case Types.DOUBLE:
				value = rs.getDouble(key);
				break;
			case Types.FLOAT:
				value = rs.getFloat(key);
				break;
			case Types.DATE:
				value = rs.getDate(key);
				break;
			case Types.TIMESTAMP:
				value = rs.getTimestamp(key);
				break;
		}
		return value;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public Connection getConnection() throws SQLException {
		return this.getDataSource().getConnection();
	}
	public int getPageCount() {
		return pageCount;
	}
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
	}
	public Page getPage() {
		return page;
	}
	public String getSql() {
		return sql;
	}
	public Object[] getParams() {
		return params;
	}
	
	public static void main(String []args){
		String sql = "select * from (select u,b from a) as b";
		System.out.println(sql.replaceAll("(select )[\\S]+( from .+)", "$1count(1)$2"));
	}

}
